package linuxbox.common.bean;


import java.util.Vector;
import java.util.Iterator;

import org.apache.log4j.Logger;

import linuxbox.exception.*;
import linuxbox.bean.*;
import linuxbox.util.*;
import linuxbox.common.*;

public class BoxUserConfigEntryBean extends BoxUserConfigEntry implements ITableBean
{
  protected DbAdapter m_db = null;
  protected boolean m_bTransaction = false;
  static protected String m_TableName = "box_user_config";
  protected static Logger logger = Logger.getLogger(BoxUserConfigEntryBean.class.getName());

  public BoxUserConfigEntryBean(BaseIntKey pk, DbAdapter db)
  {
    this.init(pk, db);
  }

  public BoxUserConfigEntryBean(BaseIntKey pk)
  {
    this.init(pk, null);
  }

  public void init(BaseIntKey pk, DbAdapter db)
  {
    if (db != null)
    {
      m_db = db;
      m_bTransaction = true;
    }
    if (pk != null)
      setID(pk.getID());
  }

  public String getTableName()
  {
    return m_TableName;
  }

  public void setTableName(String str)
  {
    m_TableName = str;
  }

  public void makeConnection() throws Exception
  {
    if ( m_db == null )
      m_db = new DbAdapter(true);
  }

  public void closeConnection() throws Exception
  {
    if (logger.isDebugEnabled())
      logger.debug("Close connection: m_db = "+m_db);
    if (m_db != null)
    {
      m_db.close();
      m_db = null;
    }
  }

  public void load() throws Exception
  {
    StringBuffer sql = new StringBuffer();

    sql.append("select id, userid, module, category, name, value from ");
    sql.append(getTableName());
    sql.append(" where id = " + getID());

    makeConnection();
    m_db.executeQuery(sql.toString());
    if (m_db.next())
    {
      m_iID = m_db.getInt("id");
      m_iUserID = m_db.getInt("userid");
      m_sModule = m_db.getString("module");
      m_sCategory = m_db.getString("category");
      m_sName = m_db.getString("name");
      m_sValue = m_db.getString("value");
    }
    else
    {
      if (!m_bTransaction)
        closeConnection();
      throw new LinuxboxException(ExceptionHelper.COMMON, "LOAD", "Load error:  m_iID=" + m_iID);
    }
    if (!m_bTransaction)
      closeConnection();
  }

  public void store() throws Exception
  {
    StringBuffer sql = new StringBuffer();
    sql.append("UPDATE ");
    sql.append(getTableName());
    sql.append(" set ");
    DBUtil.setInteger(sql, "userid", m_iUserID, true);
    DBUtil.setString(sql, "module", m_sModule, false);
    DBUtil.setString(sql, "category", m_sCategory, false);
    DBUtil.setString(sql, "name", m_sName, false);
    DBUtil.setString(sql, "value", m_sValue, false);

    sql.append(" where id = " + m_iID);
    if (logger.isDebugEnabled())
      logger.debug(sql.toString());
    makeConnection();
    m_db.executeUpdate(sql.toString());
    if (!m_bTransaction)
      closeConnection();
  }

  public void refresh() throws Exception
  {
    load();
  }

  public void create() throws Exception
  {
    if (m_iID != 0)
    {
      String sql = "INSERT INTO ";
      sql += getTableName();
      sql += " (id) VALUES(" + m_iID + ")";
      makeConnection();
      m_db.executeUpdate(sql);
    }
    else
    {
      String sql = "select max(id) from ";
      sql += getTableName();
      makeConnection();
      m_db.executeQuery(sql);
      if (m_db.next())
      {
        int max_id = m_db.getInt(1);
        max_id++;
        m_iID = max_id;
      }
      else
      {
        m_iID = 1;
      }
      sql = "insert into ";
      sql += getTableName();
      sql += " (id) values(" + m_iID + ")";
      m_db.executeUpdate(sql);
    }
    if (!m_bTransaction)
      closeConnection();
  }

  public void remove() throws Exception
  {
    String sql = "DELETE FROM ";
    sql += getTableName();
    sql += (" where id = " + m_iID);
    makeConnection();
    m_db.executeUpdate(sql);
    if (!m_bTransaction)
      closeConnection();
  }

  public Object findByKey() throws Exception
  {
    StringBuffer sql = new StringBuffer();
    sql.append("select id from ");
    sql.append(getTableName());
    sql.append(" where id = " + m_iID);
    makeConnection();
    m_db.executeQuery(sql.toString());
    BaseIntKey pk = null;
    if(m_db.next())
    {
      pk = new BaseIntKey();
      pk.setID(m_db.getInt(1));
    }
    if (!m_bTransaction)
      closeConnection();
    return pk;
  }

  public Iterator findBySql(String sqlWhere) throws Exception
  {
    Vector v = null;
    try
    {
      StringBuffer sql = new StringBuffer();
      sql.append("select id from ");
      sql.append(getTableName());
      sql.append(sqlWhere);
      makeConnection();
      m_db.executeQuery(sql.toString());
      BaseIntKey pk = null;
      v = new Vector();
      while (m_db.next())
      {
        pk = new BaseIntKey();
        pk.setID(m_db.getInt(1));
        v.addElement(pk);
      }
      if (!m_bTransaction)
        closeConnection();
    }
    catch(Exception e)
    {
      if (!m_bTransaction)
        closeConnection();
      throw new LinuxboxException(ExceptionHelper.COMMON, "FIND", "Can't findBySql: " + e.getMessage());
    }
    return v.iterator();
  }

  public String getValue(int UserID, String module, String category, String name) throws LinuxboxException
  {
    try
    {
      StringBuffer sql = new StringBuffer();
      sql.append("select * from ");
      sql.append(getTableName());
      sql.append(" where  ");
      DBUtil.setInteger(sql, "userid", UserID, true);
      sql.append(" and ");
      DBUtil.setString(sql, "module", module, true);
      sql.append(" and ");
      DBUtil.setString(sql, "category", category, true);
      sql.append(" and ");
      DBUtil.setString(sql, "name", name, true);

      m_sValue = null;

      makeConnection();
      m_db.executeQuery(sql.toString());
      if (m_db.next())
      {
        m_iID = m_db.getInt("id");
        m_iUserID = m_db.getInt("userid");
        m_sModule = m_db.getString("module");
        m_sCategory = m_db.getString("category");
        m_sName = m_db.getString("name");
        m_sValue = m_db.getString("value");
      }
      if (!m_bTransaction)
        closeConnection();
      if (logger.isDebugEnabled())
      {
        logger.debug(sql + " : " + m_sValue);
      }
    }
    catch(Exception e)
    {
      try
      {
        if (!m_bTransaction)
          closeConnection();
      }
      catch(Exception e1) {}
      throw new LinuxboxException(ExceptionHelper.COMMON, "getValue", "Can't getValue: " + e.getMessage());
    }
    return m_sValue;
  }

//Will try get it first, if not found, create a new one
  public void setValue(int UserID, String module, String category, String name, String value) throws LinuxboxException
  {
    boolean tmpTransactionFlag = m_bTransaction;
    try
    {
      m_bTransaction = true;

      //try to get from database
      StringBuffer sql = new StringBuffer();
      sql.append("select * from ");
      sql.append(getTableName());
      sql.append(" where  ");
      DBUtil.setInteger(sql, "userid", UserID, true);
      sql.append(" and ");
      DBUtil.setString(sql, "module", module, true);
      sql.append(" and ");
      DBUtil.setString(sql, "category", category, true);
      sql.append(" and ");
      DBUtil.setString(sql, "name", name, true);

      makeConnection();
      m_db.executeQuery(sql.toString());
      if (m_db.next())
      {
        m_iID = m_db.getInt("id");
        m_sModule = m_db.getString("module");
        m_sCategory = m_db.getString("category");
        m_sName = m_db.getString("name");
        m_iUserID = m_db.getInt("userid");

        m_sValue = value;
        store();
      }
      else
      {
        m_iID = 0;
        create();
        m_sModule = module;
        m_sCategory = category;
        m_sName = name;
        m_sValue = value;
        m_iUserID = UserID;
        store();
      }

      m_bTransaction = tmpTransactionFlag;
      if (!m_bTransaction)
        closeConnection();

      if (logger.isDebugEnabled())
      {
        logger.debug(sql + " : " + m_sValue);
      }
    }
    catch(Exception e)
    {
      m_bTransaction = tmpTransactionFlag;
      try
      {
        if (!m_bTransaction)
          closeConnection();
      }
      catch(Exception e1) {}

      m_bTransaction = tmpTransactionFlag;
      throw new LinuxboxException(ExceptionHelper.COMMON, "setValue", "Can't setValue: " + e.getMessage());
    }
  }

  public String[] getValues(int UserID, String module, String category, String name) throws LinuxboxException
  {
    Vector v = null;
    try
    {
      StringBuffer sql = new StringBuffer();
      sql.append("select * from ");
      sql.append(getTableName());
      sql.append(" where  ");
      DBUtil.setInteger(sql, "userid", UserID, true);
      sql.append(" and ");
      DBUtil.setString(sql, "module", module, true);
      sql.append(" and ");
      DBUtil.setString(sql, "category", category, true);
      sql.append(" and ");
      DBUtil.setString(sql, "name", name, true);

      makeConnection();
      m_db.executeQuery(sql.toString());
      v = new Vector();
      while (m_db.next())
      {
        v.add(m_db.getString("value"));
      }
      if (!m_bTransaction)
        closeConnection();

      if (logger.isDebugEnabled())
      {
        logger.debug(sql + " : " + m_sValue);
      }
    }
    catch(Exception e)
    {
      try
      {
        if (!m_bTransaction)
          closeConnection();
      }
      catch(Exception e1) {}
      throw new LinuxboxException(ExceptionHelper.COMMON, "getValues", "Can't getValues: " + e.getMessage());
    }
    if (v!=null && v.size() > 0)
    {
      String[] ret = new String[v.size()];
      return (String[]) v.toArray(ret);
    }
    else
      return null;
  }

  public void setValues(int UserID, String module, String category, String name, String[] values) throws LinuxboxException
  {
    boolean tmpTransactionFlag = m_bTransaction;
    try
    {
      m_bTransaction = true;

      //First remove all the values in database
      StringBuffer sql = new StringBuffer();
      sql.append("DELETE FROM ");
      sql.append(getTableName());
      sql.append(" where  ");
      DBUtil.setInteger(sql, "userid", UserID, true);
      sql.append(" and ");
      DBUtil.setString(sql, "module", module, true);
      sql.append(" and ");
      DBUtil.setString(sql, "category", category, true);
      sql.append(" and ");
      DBUtil.setString(sql, "name", name, true);

      makeConnection();
      m_db.executeUpdate(sql.toString());

      if (values != null && values.length > 0)
      {
        m_sModule = module;
        m_sCategory = category;
        m_sName = name;
        m_iUserID = UserID;
        for(int i=0; i < values.length; i++)
        {
          m_iID = 0;
          create();
          m_sValue = values[i];
          store();
        }
      }
      m_bTransaction = tmpTransactionFlag;
      if (!m_bTransaction)
        closeConnection();

      if (logger.isDebugEnabled())
      {
        logger.debug(sql + " : " + values);
      }
    }
    catch(Exception e)
    {
      m_bTransaction = tmpTransactionFlag;
      try
      {
        if (!m_bTransaction)
          closeConnection();
      }
      catch(Exception e1) {}
      throw new LinuxboxException(ExceptionHelper.COMMON, "setValues", "Can't setValues: " + e.getMessage());
    }
  }

  public void removeUserConfig(int UserID) throws LinuxboxException
  {
    try
    {
      StringBuffer sql = new StringBuffer();
      sql.append("DELETE FROM ");
      sql.append(getTableName());
      sql.append(" where ");
      DBUtil.setInteger(sql, "userid", UserID, true);

      makeConnection();
      m_db.executeUpdate(sql.toString());
      if (!m_bTransaction)
        closeConnection();
    }
    catch(Exception e)
    {
      try
      {
        if (!m_bTransaction)
          closeConnection();
      }
      catch(Exception e1) {}
      throw new LinuxboxException(ExceptionHelper.COMMON, "removeUserConfig", "Can't removeUserConfig: " + e.getMessage());
    }
  }
}
